package com.example.teaching_server.repository;

import com.example.teaching_server.entity.NoticeRecipientEntity;
import jakarta.transaction.Transactional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * 通知接收者数据访问层
 */
@Repository
public interface NoticeRecipientRepository extends JpaRepository<NoticeRecipientEntity, Long> {

    /**
     * 根据通知ID查找接收者
     */
    List<NoticeRecipientEntity> findByNoticeId(Long noticeId);

    /**
     * 根据用户ID和通知ID查找接收记录
     */
    @Query(value = "SELECT nr.* FROM notice_recipients nr " +
            "WHERE nr.notice_id = :noticeId " +
            "AND ((nr.recipient_type = 'class' AND :userId IN " +
            "        (SELECT u.id FROM users u WHERE u.class_id = CAST(nr.target_id AS bigint))) " +
            "   OR (nr.recipient_type = 'user' AND CAST(nr.target_id AS bigint) = :userId) " +
            "   OR (nr.recipient_type = 'role' AND :userId IN " +
            "        (SELECT u.id FROM users u WHERE u.role = CAST(nr.target_id AS text)))) " +
            "LIMIT 1",
            nativeQuery = true)
    Optional<NoticeRecipientEntity> findByNoticeIdAndUserId(@Param("noticeId") Long noticeId,
                                                            @Param("userId") Long userId);

    /**
     * 统计通知的已读数量
     */
    @Query("SELECT COUNT(nr) FROM NoticeRecipientEntity nr " +
            "WHERE nr.noticeId = :noticeId AND nr.readStatus = true")
    Long countReadRecipientsByNoticeId(@Param("noticeId") Long noticeId);

    /**
     * 统计通知的总接收者数量
     */
    @Query("SELECT COUNT(nr) FROM NoticeRecipientEntity nr " +
            "WHERE nr.noticeId = :noticeId")
    Long countTotalRecipientsByNoticeId(@Param("noticeId") Long noticeId);

    /**
     * 根据通知ID删除接收者记录
     */
    @Modifying
    @Transactional
    @Query("DELETE FROM NoticeRecipientEntity nr WHERE nr.noticeId = :noticeId")
    void deleteByNoticeId(@Param("noticeId") Long noticeId);
}
